USE [dbproc]
GO

/****** Object:  StoredProcedure [dbo].[spx_dashboard]    Script Date: 03/28/2012 13:10:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[spx_dashboard]
        @sid uniqueidentifier,
        @xml xml output
as
begin
        set nocount on;
        
        declare @uid int set @uid = null;
        declare @now datetime set @now = getdate();

        exec sp_get_user_by_session @sid, @uid output;
        
        declare @prod table(id int, title nvarchar(250));
        -- user found
        if @uid is not null
        begin
                insert into @prod
                        select top 5 id, title from products order by newid();
                        
                set @xml = (
                select 
                        (
        select s.[Id] as id
      ,dbo.fn_dt_format(s.GetTime) as GetTime       
      ,isnull(sp.[MainPhone],s.Address) as MainPhone
      ,isnull(sp.[TagPhone],s.Address) as TagPhone
      ,isnull(sp.[CmdCode], '') as CmdCode
      ,isnull(sp.[FullSum2],0) as FullSum2
      ,isnull(sp.[TransferSum2],0) as TransferSum2
      ,isnull(cast(a.Res as varchar),'') as res
      ,isnull(sp.[ServiceResultText],s.Message) + ' '+isnull(isnull(a.Result, sp.[ServiceResultText]),s.Comment)  as ServiceResultText
      ,dbo.fn_dt_format(isnull(isnull(a.DateTimePro,sp.ResultServiceTime),s.ProTime)) as DateTimePro                      
                        
  FROM 
  [sms].[dbo].[SMS] s
  left join [sms].[dbo].[SMSPay] sp on sp.sms_id=s.id
  
  left  join meps.dbo.AutomatLog a on a.sms_id=s.id
  join dbdata.dbo.phones p on ((p.phone=s.[Address]) or (p.phone=sp.[Phone]) or (p.phone=sp.[MainPhone]) or (p.phone=sp.[TagPhone]  )) 
  where  (p.uid=@uid)and 
  isnull(CmdCode,'NULL') not in ('?','PIN','DBC','ba')
  and substring(s.Message,1,3)  not in ('PIN','DBC')
  and substring(s.Message,1,2)  not in ('ba','? ')
  order by s.Id desc
                           
                             for xml raw('operation'), type) as operations,
                        (select 
                        a.id, 
                        c.title, 
                        cast(a.summary as decimal(20,2)) summary, 
                        a.enabled 
                        from accounts a, account_categs c 
                        where c.id = a.categ_id and a.uid = @uid for xml raw('account'), type) as accounts,
                        (select top 3 id, dt, title, info from promos order by newid() for xml raw('promo'), type) as promos

                  for xml raw('root')
                );
        end else
        -- unknown user 
        begin
                insert into @prod
                        select top 5 id, title from products order by newid();
                set @xml = (
                select 
                        (select id, title from @prod for xml raw('prod'), type) as products,
                        (select top 3 id, dt, title, info from promos order by newid() for xml raw('promo'), type) as promos
                  for xml raw('root')
                );
        end;
end;
go

/*
declare @xml xml
 exec dbproc.[dbo].[spx_dashboard]      '1B54333C-CD4A-4A38-B5B7-F23DEBFD8C24',
        @xml output
select @xml

*/